package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmpDaolmpl implements EmpDao {

    // 声明变量
    private Connection conn;
    private PreparedStatement pstmt;
    private ResultSet rs;

    // 添加一条员工记录
    @Override
    public void addEmp(Emp emp) {
        try {
            conn =  JDBCDemo.getConnection();  // 建立数据库连接
            String sql = "insert into emp values (?,?,?,?,?)";  // 插入语句
            pstmt = conn.prepareStatement(sql);  //创建 PreparedStatement 对象
            pstmt.setInt(1, emp.getEmpno());    // 绑定参数
            pstmt.setString(2, emp.getEname());
            pstmt.setString(3, emp.getJob());
            pstmt.setDouble(4, emp.getSal());
            pstmt.setInt(5, emp.getDeptno());
            int i = pstmt.executeUpdate();    // 执行插入操作
            if(i > 0){
                System.out.println("insert success"); // 插入成功，输出提示信息
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCDemo.closeAll(null, pstmt, conn);  // 关闭连接，释放资源
        }
    }

    // 根据员工编号查询一条员工记录
    @Override
    public Emp findEmpByEmpno(int empno) {
        Emp emp = null;
        try {
            conn = JDBCDemo.getConnection();  // 建立数据库连接
            pstmt = conn.prepareStatement("select * from emp where empno=?");  // 创建 PreparedStatement 对象，执行查询
            pstmt.setInt(1, empno);  // 绑定参数
            rs = pstmt.executeQuery();  // 执行查询操作
            if(rs.next()){
                emp = new Emp();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emp.setSal(rs.getDouble("sal"));
                emp.setDeptno(rs.getInt("deptno"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCDemo.closeAll(rs, pstmt, conn); // 关闭连接，释放资源
        }
        return emp;  // 返回查询结果
    }

    // 统计数据
    @Override
    public int totalCount() {
        int count = 0;
        try {
            conn = JDBCDemo.getConnection();
            pstmt = conn.prepareStatement("select count(*) from emp");
            rs = pstmt.executeQuery();
            if(rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCDemo.closeAll(rs, pstmt, conn);
        }
        return count;
    }

    // 删除数据
    @Override
    public void delEmpByEmpno(int empno) {
        try {
            conn = JDBCDemo.getConnection();
            pstmt = conn.prepareStatement("delete from emp where empno=?");
            pstmt.setInt(1, empno);
            int i = pstmt.executeUpdate();
            if(i>0){
                System.out.println("del success");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCDemo.closeAll(rs, pstmt, conn);
        }
    }

    // 模糊查询
    @Override
    public List<Emp> likeQueryEmps(String job) {
        List<Emp> emps = new ArrayList<Emp>();
        Emp emp = null;

        try {
            conn = JDBCDemo.getConnection();
            pstmt = conn.prepareStatement("select * from emp where job like ?");
            pstmt.setString(1, "%"+job+"%");
            rs = pstmt.executeQuery();
            while (rs.next()){
                emp = new Emp();
                emp.setEmpno(rs.getInt("empno"));
                emp.setEname(rs.getString("ename"));
                emp.setJob(rs.getString("job"));
                emp.setSal(rs.getDouble("sal"));
                emp.setDeptno(rs.getInt("deptno"));
                emps.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCDemo.closeAll(rs, pstmt, conn);
        }
        return emps;
    }
}

